﻿using QG.FrontDesk.Core;
using QG.FrontDesk.DAL.IRepository;
using QG.FrontDesk.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace QG.FrontDesk.DAL.Repository
{
    public class ResumesByPersonRepository : IResumesByPersonRepository
    {
        public List<Position_ResumeInfo> GetResumeInfo(int userId, short statu)
        {
            List<Position_ResumeInfo> resumeInfos = null;
            string sql = @"SELECT * FROM position_resumeinfo WHERE RId IN (SELECT RId FROM resumeinfo WHERE UserId = 1)";
            if (statu == 0)//投递成功的
            {
                sql += " AND position_resumeinfo.Statu !=5 ";

            }
            else if (statu == 1)//被查看的
            {
                sql += " AND IsLook=1";
            }
            else if (statu == 2)//通过初筛
            {
                sql += " AND position_resumeinfo.Statu !=1 ";
            }
            else if (statu!=-1)
            {
                sql += " AND position_resumeinfo.Statu =@Statu ";
                resumeInfos = DapperDBHelper<Position_ResumeInfo>.QueryList(sql, new { userId = userId, Statu = statu });
            }            
            if (resumeInfos == null)
            {
                resumeInfos = DapperDBHelper<Position_ResumeInfo>.QueryList(sql, new { userId = userId });
            }

            foreach (var item in resumeInfos)
            {
                //得到对应的简历信息
                sql = @"SELECT resumeInfo.`rId`,`userId`,`workId`,`experId`,`proId`,`educId`,`self_Description`,`showId`,resumeInfo.`statu` FROM position_resumeinfo
                            INNER JOIN resumeInfo ON resumeInfo.`rId`= position_ResumeInfo.`RId` WHERE position_ResumeInfo.`RId`=@RId";
                item.resumeInfo = DapperDBHelper<ResumeInfo>.QueryFirstOrDefault(sql, new { RId = item.RId });
                //得到简历下的工作经历
                sql = @"SELECT * FROM resumeInfo
                        INNER JOIN businessExperien ON businessExperien.`experId`= resumeInfo.`experId`
                        where resumeInfo.`experId`=@experId";
                item.resumeInfo.Businessexperien = DapperDBHelper<Businessexperien>.QueryFirstOrDefault(sql, new
                {
                    experId = item.resumeInfo.ExperId
                });
                //得到简历下的教育背景
                sql = @"SELECT * FROM resumeInfo
                    INNER JOIN educational ON educational.`educId`= resumeInfo.`educId`
                    WHERE educational.educId = @educId";
                item.resumeInfo.Educational = DapperDBHelper<Educational>.QueryFirstOrDefault(sql, new { educId = item.resumeInfo.EducId });
                //得到简历下的工作性质
                sql = @"SELECT * FROM resumeInfo
                        INNER JOIN ExpectToWork ON expecttowork.`workId`= resumeInfo.`workId`
                        where resumeInfo.`workId`=@workId";
                item.resumeInfo.Expecttowork = DapperDBHelper<ExpecttoWork>.QueryFirstOrDefault(sql, new { workId = item.resumeInfo.WorkId });
                //得到简历对应的用户名
                sql = @"SELECT * FROM resumeInfo
                    INNER JOIN userInfo ON userInfo.`userId`= resumeInfo.`userId`
                    WHERE resumeInfo.`userId`= @userId";
                item.resumeInfo.UserInfo = DapperDBHelper<UserInfo>.QueryFirstOrDefault(sql, new { userId = item.resumeInfo.UserId });
                //得到对应的职位信息
                sql = @"SELECT * FROM position_resumeinfo
                        INNER JOIN positionInfo ON positionInfo.`postId`= position_ResumeInfo.`PostId` where positionInfo.`postId`=@postId";
                item.position = DapperDBHelper<PositionInfo>.QueryFirstOrDefault(sql, new { postId = item.PostId });
                //得到职位名称
                sql = "SELECT * FROM postName WHERE postNameId=(SELECT postNameId FROM positionInfo WHERE PostId=@PostId)";
                item.position.Postname = DapperDBHelper<PostName>.QueryFirstOrDefault(sql, new { PostId = item.position.PostId });
                //得到公司信息
                sql = @"SELECT * FROM company
                WHERE compId = (SELECT compId FROM positioninfo WHERE postId = @postId )";
                item.position.Company = DapperDBHelper<Company>.QueryFirstOrDefault(sql, new { postId = item.position.PostId });
                //得到城市信息
                sql = "SELECT * FROM cityInfo WHERE cityId =@cityId ";
                item.position.Company.CityInfo = DapperDBHelper<Cityinfo>.QueryFirstOrDefault(sql, new { cityId = item.position.Company.CityId });
            }
            return resumeInfos;
        }
    }
}
